package csc.traning.wpsj.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import csc.traning.wpsj.domain.User;

public class UserDAO {

	public boolean addAccount(User user) throws  IllegalAccessException
	{
		boolean status=false;
		Connection connection = DatabaseInit.getConnection();
		if (getAccountByUsername(user.getUserName())==true) {
			status = false;
		}
		else
		{
			  String sql_Insert = "INSERT INTO USERS(FIRST_NAME,LAST_NAME,USER_NAME,PASSWORD,EMAIL,CELLPHONE,ROLE) "
	      	      		+ "VALUES(?,?,?,?,?,?,?)";
			  try {
				PreparedStatement pst = connection.prepareStatement(sql_Insert);
				pst.setString(1, user.getFirstName());
				pst.setString(2, user.getLastName());
				pst.setString(3, user.getUserName());
				pst.setString(4, user.getPassword());
				pst.setString(5, user.getEmailAddress());
				pst.setString(6, user.getCellphone());
				pst.setString(7, user.getRole());
				status=pst.executeUpdate()>0;
			} catch (SQLException e) {
				status=false;
				e.printStackTrace();
			}
			
		}
		return status;
		
	}
	
	//update account return true if success
	public boolean updateAccount(User user) throws  IllegalAccessException
	{
		boolean status = false;
		Connection connection = DatabaseInit.getConnection();
		String sql="UPDATE USERS SET FIRST_NAME=? ,LAST_NAME=?,EMAIL=?,PASSWORD=?,CELLPHONE=? WHERE USER_NAME=?";
		try {
			PreparedStatement pst = connection.prepareStatement(sql);
			pst.setString(1, user.getFirstName());
			pst.setString(2, user.getLastName());
			pst.setString(3, user.getEmailAddress());
			pst.setString(4, user.getPassword());
			pst.setString(5, user.getCellphone());
			pst.setString(6, user.getUserName());
			status=pst.executeUpdate()>0;
		} catch (SQLException e) {
			status = false;
			e.printStackTrace();
		}
		return status;
	}
	
	//check if username is existed, return true if username found in db
	
	public boolean getAccountByUsername(String username) throws  IllegalAccessException
	{
		Connection connection = DatabaseInit.getConnection();
		String sql ="SELECT * FROM USERS WHERE USER_NAME=?";
		boolean status=false;
		try {
			PreparedStatement pst = connection.prepareStatement(sql);
			pst.setString(1, username);
			ResultSet rs =pst.executeQuery();
			if (rs.next()) {
				status=true;
			}
			else{
				status = false;
			}
		} catch (SQLException e) {
			status= true;
			e.printStackTrace();
		}
		return status;
	}
	
	public User checkLogin(String username,String password) throws  IllegalAccessException
	{
		Connection connection = DatabaseInit.getConnection();
		String sql ="SELECT * FROM USERS WHERE USER_NAME=? AND PASSWORD=?";
		User user = null;
		try {
			PreparedStatement pst = connection.prepareStatement(sql);
			pst.setString(1, username);
			pst.setString(2, password);
			ResultSet rs =pst.executeQuery();
			if (rs.next()) {
				user=new User(rs.getString("USER_NAME"), rs.getString("PASSWORD"), rs.getString("FIRST_NAME"), rs.getString("LAST_NAME"), rs.getString("CELLPHONE"), rs.getString("EMAIL"), rs.getString("ROLE"));
				
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return user;
	}
}
